package com.yangjun.dao.impl.businessAnalysis.memberAnalysis;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.yangjun.common.realtimesource.ChartDefine;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

@Repository
public class SituationModel {
	
	 @Autowired
	 @Resource(name = "bikadJdbcTemplate")
	 private JdbcTemplate mJdbcTemplate;
	 
	 /***
	  * 平台流量总览数据
	  * @param today
	  * @param contrastday
	  * @param ordersources 平台ID链表
	  * @return
	  */
	 public List<Map<String, Object>> getOverview() { 
		 String sql = 
				 		  " SELECT "
						 +"      a.ordersource, a.cusCount, round(a.cusCount/b.totalCusCount, 4) AS cusRate, b.totalCusCount "
						 +" From "
						 +" ("
						 +"    SELECT "
						 +"          CASE WHEN A.ORDERSOURCE IN (4,26) THEN 'tmail'"
						 +"               WHEN A.ORDERSOURCE IN (1, 13, 14, 15, 21) THEN 'ownPlatform'"
						 +"               ELSE  'other'"
						 +"          END AS ordersource,"
						 +"          count(DISTINCT a.cuscode ) as cusCount"
						 +"    FROM          "
						 +"          dw_user_multi_analysis A where A.Ordersource != 999"
						 +"    GROUP BY "
						 +"            CASE WHEN A.ORDERSOURCE IN (4,26) THEN 'tmail'"
						 +"                 WHEN A.ORDERSOURCE IN (1, 13, 14, 15, 21) THEN 'ownPlatform'"
						 +"                 ELSE  'other'"
						 +"            END"
						 +" ) a,"
						 +" ( "
						 +" SELECT COUNT(DISTINCT cuscode) AS totalCusCount From  dw_user_multi_analysis where ordersource != 999"
						 +" ) b";
			    
		 List<Map<String,Object>> rows = mJdbcTemplate.queryForList(sql);
		        
		 return rows;
	 }
	 
	 public Map<String, Object> getCusCountBarGraph(String name, String legend) {
	        Map<String, Object> resultMap = Maps.newHashMap();
	        
	        String sql =
	        				" SELECT "
	        				+"      a.ordersource, a.cusCount, ROUND(a.cusCount/b.total_cnt, 4) AS cusRate "
	        				+" From "
	        				+" ("
	        				+"    SELECT "
	        				+"          org.type_name  AS ordersource, "
	        				+"          count(DISTINCT a.cuscode ) as cusCount "
	        				+"    FROM          "
	        				+"           dw_user_multi_analysis A,"
	        				+"           ("
	        				+"              SELECT * From BIDEV.DIM_KAD_VALUES V WHERE V.TABLE_NAME='dw_kad_orderitem' AND V.COLUMN_NAME='origin'"
	        				+"           ) org"
	        				+"           WHERE a.ordersource = org.type_id(+)"
	        				+"    GROUP BY "
	        				+"            org.type_name "
	        				+" ) a, "
	        				+" ( "
	        				+" SELECT COUNT(DISTINCT a.cuscode) AS total_cnt From  dw_user_multi_analysis A "
	        				+" ) b";
	        
	        // Query
	        List<Map<String,Object>> queryList = mJdbcTemplate.queryForList(sql);
	        
	        // 柱状图数据
	        String[] amountArr = new String[queryList.size()];
	        String[] platformArr = new String[queryList.size()];
	        for(int i=0, len=queryList.size(); i<len; i++) {
	            amountArr[i] = String.valueOf(queryList.get(i).get("cusCount"));
	            platformArr[i] = String.valueOf(queryList.get(i).get("ordersource"));
	            
	            if(platformArr[i].equals("IPhone"))
	            	platformArr[i] = "IOS";
	        }
	        resultMap.put("value", amountArr);
	        resultMap.put("platform", platformArr);
	        resultMap.put("name", name);
	        resultMap.put("legend", legend);
	        
	        return  resultMap;
	 }
	 
	 public Map<String,Object> getCusCountPieGraph(String ordersources) {
	        Map resultMap = Maps.newHashMap();
	        List<Map<String,String>> list = Lists.newArrayList();
	         	               
	        String sql =
	        				" SELECT "
	        				+"      a.ordersource, a.cusCount, ROUND(a.cusCount/b.total_cnt, 4) AS cusRate "
	        				+" From "
	        				+" ("
	        				+"    SELECT "
	        				+"          org.type_name  AS ordersource,"
	        				+"          count(DISTINCT a.cuscode ) as cusCount"
	        				+"    FROM          "
	        				+"           dw_user_multi_analysis A,"
	        				+"           ("
	        				+"              SELECT * From BIDEV.DIM_KAD_VALUES V WHERE V.TABLE_NAME='dw_kad_orderitem' AND V.COLUMN_NAME='origin'"
	        				+"           ) org"
	        				+"           WHERE a.ordersource = org.type_id(+)"
	        				+"    GROUP BY "
	        				+"            org.type_name"
	        				+" ) a,"
	        				+" ( "
	        				+" SELECT COUNT(DISTINCT a.cuscode) AS total_cnt From  dw_user_multi_analysis A "
	        				+" ) b";
	        
	        // Query
	        List<Map<String,Object>> queryList = mJdbcTemplate.queryForList(sql);
	        
	        // 饼图数据
	        String[] legendArr = new String[queryList.size()];
	        for(int i=0, len=queryList.size(); i<len; i++) {
	        	String platformName = String.valueOf(queryList.get(i).get("ordersource"));
	            if("IPhone".equals(platformName))
	            	platformName = "IOS";
	            
	            legendArr[i] = String.valueOf(platformName);
	            
	            Map<String,String> map = Maps.newHashMap();
	            map.put("value",String.valueOf(queryList.get(i).get("cusRate")));
	            map.put("name",String.valueOf(platformName));
	            list.add(map);
	        }
	        resultMap.put("data", list);
	        resultMap.put("legend", legendArr);
	        resultMap.put("name", "");
	        
	        return  resultMap;
	 }
	 
	 public Map<String,Object> getOrdersourceCount() {
		 String sql = "select count(distinct ordersource) from dw_user_multi_analysis";
		 
		 // Query
		 List<Map<String,Object>> queryList = mJdbcTemplate.queryForList(sql);
		 
		 return queryList.get(0);
	 }
	 
	 /**
	  * 计算区域中客户数的最大值
	  * @param platformattr
	  * @param province
	  * @return
	  */
	 public Map<String,Object> getMaxCusCount(String platformattr, String province) {
		String platformattrSQL = "";
		String provinceSQL = "";
		 
		// 拼接筛选条件
	    if (!StringUtils.isBlank(platformattr))
	    	platformattrSQL = " and ordersource = '" + platformattr + "' ";
	    
	    if (!StringUtils.isBlank(province))
	    	provinceSQL = " and province = '" + province + "' ";
		 
		 String sql = "select count(distinct cusCode) from dw_user_multi_analysis"
		 		+ " where province is not null "
				 + platformattrSQL
				 + provinceSQL;
		 
		 // Query
		 List<Map<String,Object>> queryList = mJdbcTemplate.queryForList(sql);
		 
		 return queryList.get(0);
	 }
	 
	 public Map<String, Object> getRebuyBarGraph(String name, String legend, String platformstr) {
	        Map<String, Object> resultMap = Maps.newHashMap();
	        String platformstrSQL = "";
	        
	        if (StringUtils.isNotBlank(platformstr))
	        	platformstrSQL = " and ordersource = " + platformstr;
	        
	        String sql = "select count(distinct(cuscode)) as cusCount,"
	        		+"       case"
	        		+"         when num_ordercode = 1 then"
	        		+"          '1次'"
	        		+"         when num_ordercode = 2 then"
	        		+"          '2次'"
	        		+"         when num_ordercode = 3 then"
	        		+"          '3次'"
	        		+"         when num_ordercode = 4 then"
	        		+"          '4次'"
	        		+"         when num_ordercode = 5 then"
	        		+"          '5次'"
	        		+"         when num_ordercode > 6 and num_ordercode <= 10 then"
	        		+"          '6-10次'"
	        		+"         when num_ordercode > 10 and num_ordercode <= 20 then"
	        		+"          '10-20次'"
	        		+"         else"
	        		+"          '大于20次'"
	        		+"       end as times"
	        		+"  from dw_user_multi_analysis "
	        		+"  where ordersource != 999 "
	        		+ platformstrSQL
	        		+" group by case"
	        		+"            when num_ordercode = 1 then"
	        		+"             '1次'"
	        		+"            when num_ordercode = 2 then"
	        		+"             '2次'"
	        		+"            when num_ordercode = 3 then"
	        		+"             '3次'"
	        		+"            when num_ordercode = 4 then"
	        		+"             '4次'"
	        		+"            when num_ordercode = 5 then"
	        		+"             '5次'"
	        		+"            when num_ordercode > 6 and num_ordercode <= 10 then"
	        		+"             '6-10次'"
	        		+"            when num_ordercode > 10 and num_ordercode <= 20 then"
	        		+"             '10-20次'"
	        		+"            else"
	        		+"             '大于20次'"
	        		+"          end";
	        
	        // Query
	        List<Map<String,Object>> queryList = mJdbcTemplate.queryForList(sql);
	        
	        // 柱状图数据
	        String[] amountArr = new String[queryList.size()];
	        String[] platformArr = new String[queryList.size()];
	        for(int i=0, len=queryList.size(); i<len; i++) {
	            amountArr[i] = String.valueOf(queryList.get(i).get("cusCount"));
	            platformArr[i] = String.valueOf(queryList.get(i).get("times"));
	            
	            if(platformArr[i].equals("IPhone"))
	            	platformArr[i] = "IOS";
	        }
	        resultMap.put("value", amountArr);
	        resultMap.put("platform", platformArr);
	        resultMap.put("name", name);
	        resultMap.put("legend", legend);
	        
	        return  resultMap;
	 }
	 
	 public Map<String, Object> getMemberGrowBarGraph(String year, String platformattr, String name, String legend) {
	        Map<String, Object> resultMap = Maps.newHashMap();
	        String platformattrSQL = "";
	        
	        // 拼接筛选条件
	        if (!StringUtils.isBlank(platformattr))
	        	platformattrSQL = " and ordersource = '" + platformattr + "' ";
	        
	        String sql = 
	        		" SELECT C.YEAR, C.MONTH, C.CUSCOUNT, C.CUSRATE as MONTH_MEMBER_INCREASE_RATE "
	   					 +" FROM "
	   					 +" ("
	   					 +" SELECT "
	   					 +"      AA.YEAR,AA.MONTH,AA.CUSCOUNT, AA.CUSCOUNT / BB.CUSCOUNT  AS CUSRATE "
	   					 +" From "
	   					 +" ("
	   					 +"    SELECT ROWNUM AS RN, YEAR,MONTH, CUSCOUNT  From "
	   					 +"    ("
	   					 +"          SELECT "
	   					 +"                A.YEAR,A.MONTH,COUNT(DISTINCT A.CUSCODE) AS CUSCOUNT "
	   					 +"          From dw_user_multi_analysis A "
	   					 +"          WHERE TO_NUMBER(A.YEAR) >= TO_NUMBER(TO_CHAR(TO_NUMBER(TO_CHAR(to_date('" + year + "', 'YYYY'),'YYYY'))-1)) "
	   					 +"          AND TO_NUMBER(A.MONTH) = TO_NUMBER(TO_CHAR(A.MIN_TIME,'MM')) "
	   					 + 			 platformattrSQL
	   					 +"          GROUP BY A.YEAR,A.MONTH "
	   					 +"          ORDER by A.YEAR,A.MONTH "
	   					 +"    ) A"
	   					 +" ) AA,"
	   					 +" ("
	   					 +"    SELECT ROWNUM AS RN, YEAR,MONTH, CUSCOUNT  From "
	   					 +"    ("
	   					 +"          SELECT "
	   					 +"                A.YEAR,A.MONTH,COUNT(DISTINCT A.CUSCODE) AS CUSCOUNT "
	   					 +"          From dw_user_multi_analysis A "
	   					 +"          WHERE TO_NUMBER(A.YEAR||A.MONTH) >= TO_NUMBER(TO_CHAR(TO_NUMBER(TO_CHAR(to_date('" + year + "', 'YYYY'),'YYYY'))-2)||'12')"
	   					 +"          AND TO_NUMBER(A.YEAR||A.MONTH) < TO_NUMBER(TO_CHAR(to_date('" + year + "', 'YYYY'),'YYYYMM')) "
	   					 + 			 platformattrSQL
	   					 +"          GROUP BY A.YEAR,A.MONTH "
	   					 +"          ORDER by A.YEAR,A.MONTH "
	   					 +"    ) A "
	   					 +" ) BB "
	   					 +" WHERE AA.RN = BB.RN(+) "
	   					 +" ) C WHERE C.YEAR = '" + year + "'";
	        
	        // Query
	        List<Map<String,Object>> queryList = mJdbcTemplate.queryForList(sql);
	        
	        // 柱状图数据
	        String[] amountArr = new String[queryList.size()];
	        String[] platformArr = new String[queryList.size()];
	        for(int i=0, len=queryList.size(); i<len; i++) {
	            amountArr[i] = String.valueOf(queryList.get(i).get("CUSCOUNT"));
	            platformArr[i] = String.valueOf(queryList.get(i).get("MONTH"));
	            
	            if(platformArr[i].equals("IPhone"))
	            	platformArr[i] = "IOS";
	        }
	        resultMap.put("value", amountArr);
	        resultMap.put("month", platformArr);
	        resultMap.put("name", name);
	        resultMap.put("legend", legend);
	        
	        return  resultMap;
	 }
	 
	 public Map<String, Object> getMemberGrowBarGraphYear(String platformstr, String name, String legend) {
	        Map<String, Object> resultMap = Maps.newHashMap();	    
	        
	        String platformstrSQL = "";
	        
	        // 拼接筛选条件
	        if (!StringUtils.isBlank(platformstr))
	        	platformstrSQL = "where ordersource = '" + platformstr + "'";
	        
	        String sql = "select year, "
	        		+ "count(distinct cuscode) as cusCount "
	        		+ "from dw_user_multi_analysis "
	        		+ platformstrSQL
	        		+ " group by year order by year";
	        
	        // Query
	        List<Map<String,Object>> queryList = mJdbcTemplate.queryForList(sql);
	        
	        // 柱状图数据
	        String[] amountArr = new String[queryList.size()];
	        String[] platformArr = new String[queryList.size()];
	        for(int i=0, len=queryList.size(); i<len; i++) {
	            amountArr[i] = String.valueOf(queryList.get(i).get("cusCount"));
	            platformArr[i] = String.valueOf(queryList.get(i).get("year"));
	            
	            if(platformArr[i].equals("IPhone"))
	            	platformArr[i] = "IOS";
	        }
	        resultMap.put("value", amountArr);
	        resultMap.put("year", platformArr);
	        resultMap.put("name", name);
	        resultMap.put("legend", legend);
	        
	        return  resultMap;
	 }
	 
	 public Map<String,Object> getRebuyPieGraph(String platformstr) {
	        Map resultMap = Maps.newHashMap();
	        List<Map<String,String>> list = Lists.newArrayList();
	        String platformstrSQL = "";
	        
	        if (StringUtils.isNotBlank(platformstr))
	        	platformstrSQL = " and ordersource = " + platformstr;
	         	               
	        String sql =
	        		" select A.times, A.cusCount / B.totalCusCount as cusRate "
	        		+" FROM ( "
	        		+" select count(distinct(cuscode)) as cusCount,"
	        		+"       case"
	        		+"         when num_ordercode = 1 then"
	        		+"          '1次'"
	        		+"         when num_ordercode = 2 then"
	        		+"          '2次'"
	        		+"         when num_ordercode = 3 then"
	        		+"          '3次'"
	        		+"         when num_ordercode = 4 then"
	        		+"          '4次'"
	        		+"         when num_ordercode = 5 then"
	        		+"          '5次'"
	        		+"         when num_ordercode > 6 and num_ordercode <= 10 then"
	        		+"          '6-10次'"
	        		+"         when num_ordercode > 10 and num_ordercode <= 20 then"
	        		+"          '10-20次'"
	        		+"         else"
	        		+"          '大于20次'"
	        		+"       end as times"
	        		+"  from dw_user_multi_analysis"
	        		+" where ordersource != 999 "
	        		+ platformstrSQL
	        		+" group by case"
	        		+"            when num_ordercode = 1 then"
	        		+"             '1次'"
	        		+"            when num_ordercode = 2 then"
	        		+"             '2次'"
	        		+"            when num_ordercode = 3 then"
	        		+"             '3次'"
	        		+"            when num_ordercode = 4 then"
	        		+"             '4次'"
	        		+"            when num_ordercode = 5 then"
	        		+"             '5次'"
	        		+"            when num_ordercode > 6 and num_ordercode <= 10 then"
	        		+"             '6-10次'"
	        		+"            when num_ordercode > 10 and num_ordercode <= 20 then"
	        		+"             '10-20次'"
	        		+"            else"
	        		+"             '大于20次'"
	        		+"          end"
	        		+" ) A, "
	        		+" (SELECT COUNT(DISTINCT A.CUSCODE) AS totalCusCount From dw_user_multi_analysis A) B";
	        
	        // Query
	        List<Map<String,Object>> queryList = mJdbcTemplate.queryForList(sql);
	        
	        // 饼图数据
	        String[] legendArr = new String[queryList.size()];
	        for(int i=0, len=queryList.size(); i<len; i++) {
	        	String platformName = String.valueOf(queryList.get(i).get("times"));
	            if("IPhone".equals(platformName))
	            	platformName = "IOS";
	            
	            legendArr[i] = String.valueOf(platformName);
	            
	            Map<String,String> map = Maps.newHashMap();
	            map.put("value",String.valueOf(queryList.get(i).get("cusRate")));
	            map.put("name",String.valueOf(platformName));
	            list.add(map);
	        }
	        resultMap.put("data", list);
	        resultMap.put("legend", legendArr);
	        resultMap.put("name", "");
	        
	        return  resultMap;
	 }
	 
	 public List<Map<String,Object>> getAllMonthIncreaseLineChart(String name, String year, String platformattr) {
		 List<Map<String,Object>> rows = null;
		 String sql;
		 String platformattrSQL = "";
		 String platformattrSQL_where = "";
		 
		// 拼接筛选条件
		if (!StringUtils.isBlank(platformattr)) {
			platformattrSQL = " and ordersource = '" + platformattr + "' ";
			platformattrSQL_where = " where ordersource = '" + platformattr + "' ";
		}
		 
		sql = 
				 		  " SELECT AA.YEAR || '-' || AA.MONTH as data_date,"
						 +"       ROUND(AA.CUSCOUNT / BB.CUSCOUNT, 4) AS NEW_RATE,"
						 +"       ROUND(CC.CUSCOUNT / DD.CUSCOUNT, 4) AS FG_RATE,"
						 +"       DD.CUSCOUNT "
						 +"  From (SELECT ROWNUM AS RN, YEAR, MONTH, CUSCOUNT"
						 +"          From (SELECT A.YEAR, A.MONTH, COUNT(DISTINCT A.CUSCODE) AS CUSCOUNT"
						 +"                  From dw_user_multi_analysis A"
						 +"                 WHERE TO_NUMBER(A.MONTH) ="
						 +"                       TO_NUMBER(TO_CHAR(A.MIN_TIME, 'MM'))"
						 + 						  platformattrSQL
						 +"                 GROUP BY A.YEAR, A.MONTH"
						 +"                 ORDER by A.YEAR, A.MONTH) A) AA,"
						 +"       (SELECT 1 AS RN, NULL AS YEAR, NULL AS MONTH, NULL AS CUSCOUNT"
						 +"          From DUAL"
						 +"        UNION ALL"
						 +"        SELECT ROWNUM + 1 AS RN, YEAR, MONTH, CUSCOUNT"
						 +"          From (SELECT A.YEAR, A.MONTH, COUNT(DISTINCT A.CUSCODE) AS CUSCOUNT"
						 +"                  From dw_user_multi_analysis A"
						 + 					platformattrSQL_where
						 +"                 GROUP BY A.YEAR, A.MONTH"
						 +"                 ORDER by A.YEAR, A.MONTH) A) BB,"
						 +"       (SELECT ROWNUM AS RN, YEAR, MONTH, CUSCOUNT"
						 +"          From (SELECT A.YEAR, A.MONTH, COUNT(DISTINCT A.CUSCODE) AS CUSCOUNT"
						 +"                  From dw_user_multi_analysis A"
						 +"                 WHERE A.NUM_ORDERCODE > 1"
						 + 					platformattrSQL
						 +"                 GROUP BY A.YEAR, A.MONTH"
						 +"                 ORDER by A.YEAR, A.MONTH) A) CC,"
						 +"       (SELECT ROWNUM AS RN, YEAR, MONTH, CUSCOUNT"
						 +"          From (SELECT A.YEAR, A.MONTH, COUNT(DISTINCT A.CUSCODE) AS CUSCOUNT"
						 +"                  From dw_user_multi_analysis A"
						 + 					platformattrSQL_where
						 +"                 GROUP BY A.YEAR, A.MONTH"
						 +"                 ORDER by A.YEAR, A.MONTH) A) DD"
						 +" WHERE AA.RN = BB.RN(+)"
						 +"   AND AA.RN = CC.RN(+)"
						 +"   AND AA.RN = DD.RN(+)"; 		 
		 
		// Query
		if (!sql.isEmpty()) {			
			rows = mJdbcTemplate.queryForList(sql);
		}
		        
		return rows;
	}
	 
	 public List<Map<String,Object>> getLineChartData(String name, String year, String platformattr, String rebuyNum) {
		 List<Map<String,Object>> rows = null;
		 String sql = "";
		 String platformattrSQL = "";
		 String platformattrSQL_when = "";
		 String yearSQL = "";
		 
		 // 拼接筛选条件
		 if (!StringUtils.isBlank(platformattr)) {
			 platformattrSQL = " and ordersource = '" + platformattr + "' ";
			 platformattrSQL_when = " where ordersource = '" + platformattr + "' ";
		 }	 
		 
		 if (!StringUtils.isBlank(year))
			 yearSQL = " and year = '" + year + "' ";
			
		 if (name == ChartDefine.MONTH_MEMBER_INCREASE_RATE) {
			 sql = 
					 /*"select t.month, round((t.cusCount - t.lastMonthCusCount) / t.lastMonthCusCount, 4) as MONTH_MEMBER_INCREASE_RATE"
					 +"  from (select month,"
					 +"               count(distinct cuscode) as cusCount,"
					 +"               lag(count(distinct cuscode), 1, count(distinct cuscode)) over(order by month) as lastMonthCusCount"
					 +"          from dw_user_multi_analysis"
					 +"         where num_ordercode = 1"
					 +"           and year = '" + year + "'"
					 + platformattrSQL
					 +"         group by month) t order by t.month";*/
			 
					 " SELECT C.YEAR, C.MONTH, C.CUSCOUNT, C.CUSRATE as MONTH_MEMBER_INCREASE_RATE "
					 +" FROM "
					 +" ("
					 +" SELECT "
					 +"      AA.YEAR,AA.MONTH,AA.CUSCOUNT, AA.CUSCOUNT / BB.CUSCOUNT  AS CUSRATE "
					 +" From "
					 +" ("
					 +"    SELECT ROWNUM AS RN, YEAR,MONTH, CUSCOUNT  From "
					 +"    ("
					 +"          SELECT "
					 +"                A.YEAR,A.MONTH,COUNT(DISTINCT A.CUSCODE) AS CUSCOUNT "
					 +"          From dw_user_multi_analysis A "
					 +"          WHERE TO_NUMBER(A.YEAR) >= TO_NUMBER(TO_CHAR(TO_NUMBER(TO_CHAR(to_date('" + year + "', 'YYYY'),'YYYY'))-1)) "
					 +"          AND TO_NUMBER(A.MONTH) = TO_NUMBER(TO_CHAR(A.MIN_TIME,'MM')) "
					 + 			 platformattrSQL
					 +"          GROUP BY A.YEAR,A.MONTH "
					 +"          ORDER by A.YEAR,A.MONTH "
					 +"    ) A"
					 +" ) AA,"
					 +" ("
					 +"    SELECT ROWNUM AS RN, YEAR,MONTH, CUSCOUNT  From "
					 +"    ("
					 +"          SELECT "
					 +"                A.YEAR,A.MONTH,COUNT(DISTINCT A.CUSCODE) AS CUSCOUNT "
					 +"          From dw_user_multi_analysis A "
					 +"          WHERE TO_NUMBER(A.YEAR||A.MONTH) >= TO_NUMBER(TO_CHAR(TO_NUMBER(TO_CHAR(to_date('" + year + "', 'YYYY'),'YYYY'))-2)||'12')"
					 +"          AND TO_NUMBER(A.YEAR||A.MONTH) < TO_NUMBER(TO_CHAR(to_date('" + year + "', 'YYYY'),'YYYYMM')) "
					 + 			 platformattrSQL
					 +"          GROUP BY A.YEAR,A.MONTH "
					 +"          ORDER by A.YEAR,A.MONTH "
					 +"    ) A "
					 +" ) BB "
					 +" WHERE AA.RN = BB.RN(+) "
					 +" ) C WHERE C.YEAR = '" + year + "'";
		 } else if (name == ChartDefine.YEAR_MEMBER_INCREASE_RATE) {
			 sql = "select t.year, round((t.cusCount - t.lastMonthCusCount) / t.lastMonthCusCount, 4) as YEAR_MEMBER_INCREASE_RATE"
					 +"  from (select year,"
					 +"               count(distinct cuscode) as cusCount,"
					 +"               lag(count(distinct cuscode), 1, count(distinct cuscode)) over(order by year) as lastMonthCusCount"
					 +"          from dw_user_multi_analysis"
					 +"         where num_ordercode = 1"
					 + platformattrSQL
					 +"         group by year) t order by t.year";
		 } else if (name == ChartDefine.ALL_MEMBER_INCREASE_RATE) {
			 sql = "select data_date, round((t.cusCount - t.lastMonthCusCount) / t.lastMonthCusCount, 4) as ALL_MEMBER_INCREASE_RATE"
					 +"  from (select year || '-' || month as data_date,"
					 +"               count(distinct cuscode) as cusCount,"
					 +"               lag(count(distinct cuscode), 1, count(distinct cuscode)) over(order by year) as lastMonthCusCount"
					 +"          from dw_user_multi_analysis"
					 +"         where num_ordercode = 1"
					 + platformattrSQL
					 +"         group by year, month) t";
		 } else if (name == ChartDefine.ALL_REBUY_RATE) {
			 sql = "select t.data_date, round((t.cusCount - t.lastMonthCusCount) / t.lastMonthCusCount, 4) as ALL_REBUY_RATE"
					 +"  from (select year || '-' || month as data_date,"
					 +"               count(distinct cuscode) as cusCount,"
					 +"               lag(count(distinct cuscode), 1, count(distinct cuscode)) over(order by year) as lastMonthCusCount"
					 +"          from dw_user_multi_analysis"
					 +"         where num_ordercode > 1"
					 + platformattrSQL
					 +"         group by year, month) t order by t.data_date";
		 }  else if (name == ChartDefine.ALL_ORDER_COUNT) {
			 sql = "select year || '-' || month as data_date, "
			 		+ "sum(num_ordercode) as ALL_ORDER_COUNT "
			 		+ "from dw_user_multi_analysis "
			 		+ platformattrSQL_when
			 		+ "group by year, month order by data_date";
		 } else if (name == ChartDefine.ALL_NEW_CUS_COUNT) {
			 sql = "select year || '-' || month as data_date, "
			 		+ "count(distinct cuscode) as ALL_NEW_CUS_COUNT "
			 		+ "from dw_user_multi_analysis "
			 		+ "where num_ordercode = '" + rebuyNum + "' "
			 		+ platformattrSQL
			 		+ "group by year, month order by data_date";
		 } else if (name == ChartDefine.ALL_MEMBER_INCREASE_RATE_REBUY) {
			 sql = "select t.data_date, round((t.cusCount - t.lastMonthCusCount) / t.lastMonthCusCount, 4) as ALL_MEMBER_INCREASE_RATE_REBUY"
					 +"  from (select year || '-' || month as data_date,"
					 +"               count(distinct cuscode) as cusCount,"
					 +"               lag(count(distinct cuscode), 1, count(distinct cuscode)) over(order by year) as lastMonthCusCount"
					 +"          from dw_user_multi_analysis"
					 +"         where num_ordercode = '" + rebuyNum + "' "
					 + platformattrSQL
					 +"         group by year, month) t order by t.data_date";
		 } else if (name == ChartDefine.ALL_SLEEP_CUS_COUNT) {
			 sql = "select year || '-' || month as data_date, "
			 		+ "count(distinct cuscode) as ALL_SLEEP_CUS_COUNT "
			 		+ "from dw_user_multi_analysis "
			 		+ "where last_day(to_date(year || '-' || month, 'yyyy-mm')) >= trunc(max_time) "
			 		+ platformattrSQL
			 		+ "group by year, month";
		 } else {
			
		 }
		 
		// Query
		if (!sql.isEmpty()) {			
		    rows = mJdbcTemplate.queryForList(sql);
		}
	        
	    return rows;
	}
}















